class Nationality < ActiveRecord::Base
	has_many :tour_groups
  
  validates_presence_of :name, :message => "请输入国籍名称"
  validates_uniqueness_of :name, :message => "已经存在同名国籍"
  
  def Nationality.tour_group_count()
    Nationality.find_by_sql("SELECT n1.id, n1.name, a.tour_group_count, a.people_count, a.children_count
    FROM nationalities n1 LEFT OUTER JOIN
    (SELECT n.id, COUNT(*) AS tour_group_count, SUM(people) AS people_count, SUM(children) as children_count
    FROM nationalities N INNER JOIN tour_groups t ON t.nationality_id = n.id
    GROUP BY n.id) AS a ON n1.id = a.id
    ORDER BY a.tour_group_count DESC")
  end
end
